﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.OleDb;
using System.Data;
using System.IO;
using System.Text;

namespace WEALTH.Manager.Customer
{
    public partial class ExcelLoad : BasePage
    {
        private ExcuteDao ExcelManage = new ExcuteDao();
        private ExcelFilter excelFilter = new ExcelFilter();

        #region 页面初始化
        /// <summary>
        /// 页面加载
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void Page_Load(object sender, EventArgs e)
        {
            if (Request.QueryString["UrlType"] != null && Request.QueryString["UrlType"].Equals("menu"))
            {
                Session["SortExpression_ExcelList"] = null;
                Session["SortDirection_ExcelList"] = null;
                Session["#txtCPDM"] = null;
                Session["#txtCPName"] = null;
                Session["#txtFXJG"] = null;
                Session["#txtCPType"] = null;
                Session["#txtLJBX"] = null;
                Session["#txtSYL"] = null;
                Session["#txtCPQX"] = null;

            }

            if (!string.IsNullOrEmpty(Request.QueryString["pageIndex"]))
            {
                string pageIndex = Request.QueryString["pageIndex"];
                int index = Convert.ToInt32(pageIndex);
                gvExcel.PageIndex = index;

                Session["ExcelListIndex"] = index;

                setSessionData();
            }
            //设置分页状态
            if (Session["SortExpression_ExcelList"] != null)
            {
                this.gvExcel.Attributes["SortExpression_ExcelList"] = Session["SortExpression_ExcelList"].ToString();
                this.gvExcel.Attributes["SortDirection_ExcelList"] = Session["SortDirection_ExcelList"].ToString();
                setSortColumnStyle(this.gvExcel, Session["SortExpression_ExcelList"].ToString(), Session["SortDirection_ExcelList"].ToString());
            }

            if (!IsPostBack)
            {
                setSearchData();
                ViewState["ExcelAll"] = ExcelManage.Get_CustomerChanPinList_ALL(excelFilter);
                SearchExcelBindData();
            }
        }
        private void setSearchData()
        {
            excelFilter = new ExcelFilter();
            excelFilter.CP_Code = SpecialCharHandler.HtmlEncode(txtCPDM.Text);
            excelFilter.CP_Name = SpecialCharHandler.HtmlEncode(txtCPName.Text);
            excelFilter.CP_FXJG = SpecialCharHandler.HtmlEncode(txtFXJG.Text);
            excelFilter.CP_Type = SpecialCharHandler.HtmlEncode(txtCPType.Text);
            excelFilter.CP_leijibiaoxian = SpecialCharHandler.HtmlEncode(txtLJBX.Text);
            excelFilter.CP_Shouyilv = SpecialCharHandler.HtmlEncode(txtSYL.Text);
            excelFilter.CP_Qixian = SpecialCharHandler.HtmlEncode(txtCPQX.Text);
        }
        private void setSessionData()
        {
            //检索条件记录
            if (Session["txtCPDM"] != null)
            {
                txtCPDM.Text = SpecialCharHandler.HtmlDiscode(Session["txtCPDM"].ToString());
            }

            if (Session["txtCPName"] != null)
            {
                txtCPName.Text = SpecialCharHandler.HtmlDiscode(Session["txtCPName"].ToString());
            }

            if (Session["txtFXJG"] != null)
            {
                txtFXJG.Text = SpecialCharHandler.HtmlDiscode(Session["txtFXJG"].ToString());
            }

            if (Session["txtCPType"] != null)
            {
                txtCPType.Text = SpecialCharHandler.HtmlDiscode(Session["txtCPType"].ToString());
            }

            if (Session["txtLJBX"] != null)
            {
                txtLJBX.Text = SpecialCharHandler.HtmlDiscode(Session["txtLJBX"].ToString());
            }

            if (Session["txtSYL"] != null)
            {
                txtSYL.Text = SpecialCharHandler.HtmlDiscode(Session["txtSYL"].ToString());
            }

            if (Session["txtCPQX"] != null)
            {
                txtCPQX.Text = SpecialCharHandler.HtmlDiscode(Session["txtCPQX"].ToString());
            }

            if (Session["ExcelListIndex"] != null)
            {
                gvExcel.PageIndex = (int)Session["ExcelListIndex"];
            }
        }
        #endregion

        #region 信息列表事件

        /// <summary>
        /// 信息列表数据加载时触发
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void gvExcel_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                e.Row.Attributes.Add("id", e.Row.RowIndex.ToString());
                e.Row.Attributes.Add("onKeyDown", "SelectRow();");
                e.Row.Attributes.Add("onClick", "MarkRow(" + e.Row.RowIndex.ToString() + ");");

                //((HyperLink)e.Row.Cells[0].Controls[0]).NavigateUrl = "../Customer/EditExcel.aspx?ExcelID="
                //    + SpecialCharHandler.HtmlDiscode(((HyperLink)e.Row.Cells[0].Controls[0]).Text).Replace("&", "amp;");
                if (e.Row.Cells[5].Text.Length > 0 && !e.Row.Cells[5].Text.Equals("&nbsp;"))
                {
                    e.Row.Cells[5].Text = SpecialCharHandler.HtmlDiscode(e.Row.Cells[5].Text) + " 天";
                }
                if (e.Row.Cells[6].Text.Length > 0 && !e.Row.Cells[6].Text.Equals("&nbsp;"))
                {
                    e.Row.Cells[6].Text = SpecialCharHandler.HtmlDiscode(Math.Round((Convert.ToDouble(e.Row.Cells[6].Text) * 100), 2).ToString("0.00")) + "%";
                }
                if (e.Row.Cells[10].Text.Length > 0 && !e.Row.Cells[10].Text.Equals("&nbsp;"))
                {
                    e.Row.Cells[10].Text = SpecialCharHandler.HtmlDiscode(Math.Round((Convert.ToDouble(e.Row.Cells[10].Text) * 100), 2).ToString("0.00")) + "%";
                }
                //string jianjie = SpecialCharHandler.HtmlDiscode(e.Row.Cells[2].Text);
                //e.Row.Cells[2].ToolTip = jianjie;
                //if (jianjie.Length > 15)
                //{
                //    jianjie = jianjie.Substring(0, 15) + "...";
                //}
                //e.Row.Cells[2].Text = jianjie;
                //e.Row.Cells[3].Text = SpecialCharHandler.HtmlDiscode(e.Row.Cells[3].Text);
                //e.Row.Cells[4].Text = SpecialCharHandler.HtmlDiscode(e.Row.Cells[4].Text);

            }
        }
        #endregion

        #region 搜索按钮事件
        /// <summary>
        /// 搜索按钮事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnSearch_Click(object sender, EventArgs e)
        {
            ViewState.Clear();
            setSearchData();
            ViewState["ExcelAll"] = ExcelManage.Get_CustomerChanPinList_ALL(excelFilter);
            //清空分页状态
            Session["SortExpression_ExcelList"] = null;
            Session["SortDirection_ExcelList"] = null;

            //检索条件记录
            Session["txtCPDM"] = txtCPDM.Text;
            Session["txtCPName"] = txtCPName.Text;
            Session["txtFXJG"] = txtFXJG.Text;
            Session["txtCPType"] = txtCPType.Text;
            Session["txtLJBX"] = txtLJBX.Text;
            Session["txtSYL"] = txtSYL.Text;
            Session["txtCPQX"] = txtCPQX.Text;

            SearchExcelBindData();

        }
        #endregion

        #region 列表功能
        /// <summary>
        /// 选择页面功能
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void gvExcel_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            this.gvExcel.PageIndex = e.NewPageIndex;
            pageIndex = e.NewPageIndex;
            Session["ExcelListIndex"] = pageIndex;
            SearchExcelBindData();
        }

        /// <summary>
        /// GridView排序事件
        /// </summary>
        protected void gvExcel_Sorting(object sender, GridViewSortEventArgs e)
        {
            // 从事件参数获取排序数据列
            string SortExpression_ExcelList = e.SortExpression.ToString();
            //// 假定为排序方向为“顺序”
            string SortDirection_ExcelList = "ASC";
            // “ASC”与事件参数获取到的排序方向进行比较，进行GridView排序方向参数的修改
            if (SortExpression_ExcelList == this.gvExcel.Attributes["SortExpression_ExcelList"])
            {
                //获得下一次的排序状态
                SortDirection_ExcelList = (this.gvExcel.Attributes["SortDirection_ExcelList"].ToString() == SortDirection_ExcelList ? "DESC" : "ASC");
            }
            // 重新设定GridView排序数据列及排序方向
            this.gvExcel.Attributes["SortExpression_ExcelList"] = SortExpression_ExcelList;
            this.gvExcel.Attributes["SortDirection_ExcelList"] = SortDirection_ExcelList;
            //记录分页状态
            Session["SortExpression_ExcelList"] = SortExpression_ExcelList;
            Session["SortDirection_ExcelList"] = SortDirection_ExcelList;

            setSortColumnStyle(this.gvExcel, SortExpression_ExcelList, SortDirection_ExcelList);
            this.SearchExcelBindData();
        }

        /// <summary>
        /// 绑定到GridView
        /// </summary>
        private void SearchExcelBindData()
        {
            // 获取GridView排序数据列及排序方向
            string SortExpression_ExcelList = this.gvExcel.Attributes["SortExpression_ExcelList"];
            string SortDirection_ExcelList = this.gvExcel.Attributes["SortDirection_ExcelList"];
            // 调用业务数据获取方法
            DataTable dtBind = (DataTable)ViewState["ExcelAll"];

            //页面数据总数
            recordCount = dtBind.Rows.Count;

            // 根据GridView排序数据列及排序方向设置显示的默认数据视图
            if ((!string.IsNullOrEmpty(SortExpression_ExcelList)) && (!string.IsNullOrEmpty(SortDirection_ExcelList)))
            {
                dtBind.DefaultView.Sort = string.Format("{0} {1}", SortExpression_ExcelList, SortDirection_ExcelList);
            }
            // GridView绑定并显示数据
            this.gvExcel.DataSource = dtBind;
            this.gvExcel.DataBind();

            pageCount = gvExcel.PageCount;
            pageIndex = gvExcel.PageIndex;

        }
        protected void gvExcel_DataBound(object sender, EventArgs e)
        {
            if (Session["Edit_ExcelID"] != null)
            {
                foreach (GridViewRow gvr in gvExcel.Rows)
                {
                    if (((HyperLink)gvr.Cells[0].Controls[0]).Text.Equals(
                        SpecialCharHandler.HtmlDiscode(Session["Edit_ExcelID"].ToString())))
                    {
                        //gvExcel.SelectedIndex = gvr.RowIndex;
                        //gvExcel.Rows[gvr.RowIndex].BackColor = System.Drawing.Color.FromName("#D8EBF0");
                        ClientScript.RegisterStartupScript(GetType(), "SelectRow", "<script>MarkRow(" + gvr.RowIndex + "); </script>");

                    }
                }
                Session["Edit_ExcelID"] = null;
            }
        }
        #endregion

        #region 删除按钮
        /// <summary>
        /// 删除按钮
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnSubmit_Click(object sender, EventArgs e)
        {

            int isok = 0;

            bool ischecked = false;
            for (int i = 0; i <= gvExcel.Rows.Count - 1; i++)
            {
                CheckBox CheckBox = (CheckBox)gvExcel.Rows[i].FindControl("chkdelete");
                if (CheckBox.Checked == true)
                {
                    string Excelid = ((HyperLink)gvExcel.Rows[i].Cells[0].Controls[0]).Text;
                    isok = ExcelManage.Delete_Excel(Convert.ToInt32(Excelid));
                    ischecked = true;
                }

            }
            if (ischecked)
            {
                ischecked = false;
                if (isok == -1)
                {
                    ShowMsg("删除信息成功！", 0);
                    setSearchData();
                    ViewState["ExcelAll"] = ExcelManage.Get_CustomerChanPinList_ALL(excelFilter);
                    SearchExcelBindData();
                }
                else
                {
                    ShowMsg("删除信息失败！", 1);
                }
            }
        }
        #endregion

        #region Excel上传
        /// <summary>
        /// 上传EXCEL
        /// </summary>
        /// <param name="fu"></param>
        /// <param name="path"></param>
        private void openExcel(FileUpload fu, String path)
        {
            String sConnectionString = string.Empty;

            // Create connection string variable. Modify the "Data Source"
            // parameter as appropriate for your environment.
            string fileleixing = Path.GetExtension(fu.FileName).ToLower(); //获取文件的扩展名

            sConnectionString = "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" +
               path +
               ";Extended Properties='Excel 12.0;HDR=yes;IMEX=1'";

            // Create connection object by using the preceding connection string.
            OleDbConnection objConn = new OleDbConnection(sConnectionString);

            // Open connection with the database.
            objConn.Open();

            // The code to follow uses a SQL SELECT command to display the data from the worksheet.

            // Create new OleDbCommand to return data from worksheet.
            OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [Sheet1$]", objConn);

            // Create new OleDbDataAdapter that is used to build a DataSet
            // based on the preceding SQL SELECT statement.
            OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();

            // Pass the Select command to the adapter.
            objAdapter1.SelectCommand = objCmdSelect;

            // Create new DataSet to hold Excel from the worksheet.
            DataSet objDataset1 = new DataSet();

            // Fill the DataSet with the Excel from the worksheet.
            objAdapter1.Fill(objDataset1, "XLData");

            // Bind data to DataGrid control.
            //DataGrid1.DataSource = objDataset1.Tables[0].DefaultView;
            //DataGrid1.DataBind();
            UploadExcel(objDataset1.Tables[0]);
            // Clean up objects.
            objConn.Close();
        }

        private void UploadExcel(DataTable exceldt)
        {
            try
            {
                if (exceldt == null || exceldt.Rows.Count < 1) { return; }

                int insertRtn = 0;
                for (int i = 0; i < exceldt.Rows.Count; i++)
                {
                    excelFilter = new ExcelFilter();

                    excelFilter.CP_Code = exceldt.Rows[i]["产品代码"].ToString();

                    if (excelFilter.CP_Code.Trim().Length > 0)
                    {
                        excelFilter.CP_Name = exceldt.Rows[i]["产品名称"].ToString();

                        if (ddlCPType.SelectedItem.Text.Equals("银行理财产品") ||
                            ddlCPType.SelectedItem.Text.Equals("信托产品") ||
                            ddlCPType.SelectedItem.Text.Equals("阳光私募产品") ||
                            ddlCPType.SelectedItem.Text.Equals("私募股权产品"))
                        {
                            excelFilter.CP_Qixiri = exceldt.Rows[i]["起息日"].ToString().Replace("00:00:00", "");

                            excelFilter.CP_Daoqiri = exceldt.Rows[i]["到期日"].ToString().Replace("00:00:00", "");

                            excelFilter.CP_Qixian = exceldt.Rows[i]["产品期限"].ToString();

                            excelFilter.CP_Shouyilv = exceldt.Rows[i]["参考收益率"].ToString();
                        }
                        if (ddlCPType.SelectedItem.Text.Equals("基金产品") ||
                              ddlCPType.SelectedItem.Text.Equals("TOT/FOF产品") ||
                              ddlCPType.SelectedItem.Text.Equals("境外理财产品"))
                        {
                            excelFilter.CP_riqi = exceldt.Rows[i]["日期"].ToString().Replace("00:00:00", "");

                            excelFilter.CP_shengoujia = exceldt.Rows[i]["申购价"].ToString();

                            excelFilter.CP_shuhuijia = exceldt.Rows[i]["赎回价"].ToString();
                            double shengoujia = 0;
                            double shuhuijia = 0;
                            double average = 0;
                            try
                            {
                                if (exceldt.Rows[i]["申购价"].ToString().Length > 0 && exceldt.Rows[i]["赎回价"].ToString().Length > 0)
                                {
                                    shengoujia = Convert.ToDouble(exceldt.Rows[i]["申购价"]);
                                    shuhuijia = Convert.ToDouble(exceldt.Rows[i]["赎回价"]);

                                    average = AppFunction.Average(new Double[] { shengoujia, shuhuijia });
                                    average = (average - 1);

                                    average = Math.Round(average, 4);
                                }
                            }
                            catch
                            {
                                average = 0;
                            }
                            if (average != 0)
                            {
                                excelFilter.CP_leijibiaoxian = average.ToString();// exceldt.Rows[i]["累计表现"].ToString();
                            }
                            excelFilter.CUSCP_SHFL = exceldt.Rows[i]["赎回费率"].ToString();

                            excelFilter.CUSCP_SGFL = exceldt.Rows[i]["申购费率"].ToString();
                        }

                        excelFilter.CP_Type = exceldt.Rows[i]["产品类型"].ToString();

                        if (ddlCPType.SelectedItem.Text.Equals("境外理财产品"))
                        {
                            excelFilter.CUSCP_JWTZGLR = exceldt.Rows[i]["境外投资管理人"].ToString();
                        }
                        else
                        {
                            excelFilter.CP_FXJG = exceldt.Rows[i]["发行机构"].ToString();
                        }

                        if (ddlCPType.SelectedItem.Text.Equals("银行理财产品"))
                        {
                            excelFilter.CUSCP_XSGLFL = exceldt.Rows[i]["销售管理费率"].ToString();

                            excelFilter.CUSCP_XSRYYJKL = exceldt.Rows[i]["销售人员佣金扣率"].ToString();
                        }

                        if (ddlCPType.SelectedItem.Text.Equals("信托产品"))
                        {
                            excelFilter.CUSCP_XSGLFL = exceldt.Rows[i]["销售管理费率"].ToString();

                            excelFilter.CUSCP_XSRYYJKL = exceldt.Rows[i]["销售人员佣金扣率"].ToString();

                            excelFilter.CUSCP_XTFL = exceldt.Rows[i]["信托费率"].ToString();
                        }

                        if (ddlCPType.SelectedItem.Text.Equals("阳光私募产品") ||
                            ddlCPType.SelectedItem.Text.Equals("私募股权产品"))
                        {
                            excelFilter.CUSCP_MKSYL = exceldt.Rows[i]["门槛收益率"].ToString();

                            excelFilter.CUSCP_CESYL = exceldt.Rows[i]["超额收益率"].ToString();
                        }
                        excelFilter.CUSCP_XSRY = exceldt.Rows[i]["销售人员"].ToString();

                        excelFilter.CUSCP_XSRYFY = exceldt.Rows[i]["销售人员返佣"].ToString();

                        excelFilter.CUSCP_JLR = exceldt.Rows[i]["净利润"].ToString();

                        excelFilter.CUSCP_CSSGFE = exceldt.Rows[i]["初始申购份额"].ToString();

                        excelFilter.CUSCP_CSSGJE = exceldt.Rows[i]["初始申购价格"].ToString();

                        excelFilter.CUSCP_CSCB = exceldt.Rows[i]["初始成本"].ToString();

                        excelFilter.CUSCP_DQJG = exceldt.Rows[i]["当前价格"].ToString();

                        excelFilter.CUSCP_SYFE = exceldt.Rows[i]["剩余份额"].ToString();

                        excelFilter.CUSCP_CPSZ = exceldt.Rows[i]["产品市值"].ToString();

                        excelFilter.CUSCP_M_Name = exceldt.Rows[i]["会员代码"].ToString();

                        excelFilter.CUSCP_KHXM = exceldt.Rows[i]["客户姓名"].ToString();

                        excelFilter.CUSCP_ZJType = exceldt.Rows[i]["证件类型"].ToString();

                        excelFilter.CUSCP_ZJH = exceldt.Rows[i]["身份证、护照号码"].ToString();

                        excelFilter.CUSCP_HYJF = exceldt.Rows[i]["会员积分"].ToString();

                        excelFilter.CP_ZhongLei = ddlCPType.SelectedItem.Text;

                        excelFilter.AddUser = getCurrentUser().UserID;

                        insertRtn = ExcelManage.Insert_CustomerChanPinList(excelFilter);
                    }
                }

                if (insertRtn != 0)
                {
                    ShowMsg("上传成功", 0);
                    setSearchData();
                    ViewState["ExcelAll"] = ExcelManage.Get_CustomerChanPinList_ALL(excelFilter);
                    SearchExcelBindData();
                }
                else
                {
                    ShowMsg("上传文件内容或格式不正确", 1);
                }
            }
            catch (Exception ex)
            {
                ShowMsg("上传文件格式不正确。" + ex.Message, 1);
                AppFunction.SaveExceptionLog(ex);
            }

        }
        /// <summary>
        /// 上传按钮事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void Button1_Click(object sender, EventArgs e)
        {
            try
            {
                if (CheckFile(FileUpload1))
                {
                    //检测一下目录是否存在
                    if (!Directory.Exists(HttpContext.Current.Request.PhysicalApplicationPath + "Excel//"))
                    {
                        Directory.CreateDirectory(HttpContext.Current.Request.PhysicalApplicationPath + "Excel//");
                    }
                    string savePath = @"\Excel\";
                    if (FileUpload1.HasFile)
                    {
                        savePath += FileUpload1.FileName;
                    }

                    if (File.Exists(HttpContext.Current.Request.PhysicalApplicationPath + "Excel//" + FileUpload1.FileName))
                    {
                        File.Delete(HttpContext.Current.Request.PhysicalApplicationPath + "Excel//" + FileUpload1.FileName);
                    }

                    string temppath = Server.MapPath("~/") + @"\Excel\" + FileUpload1.FileName;

                    FileUpload1.PostedFile.SaveAs(temppath);
                    Response.Clear();

                    openExcel(FileUpload1, temppath);

                    if (File.Exists(HttpContext.Current.Request.PhysicalApplicationPath + "Excel//" + FileUpload1.FileName))
                    {
                        File.Delete(HttpContext.Current.Request.PhysicalApplicationPath + "Excel//" + FileUpload1.FileName);
                    }
                }
            }
            catch (Exception ex)
            {
                ShowMsg("上传失败！", 1);
                AppFunction.SaveExceptionLog(ex);
            }
        }
        private bool CheckFile(FileUpload fileupload)
        {
            if (!fileupload.HasFile)
            {
                ShowMsg("请选择文件！", 1);
                return false;
            }
            string fileleixing = System.IO.Path.GetExtension(fileupload.FileName).ToLower(); //获取文件的扩展名
            if (fileleixing != ".xls" && fileleixing != ".xlsx")
            {
                ShowMsg("请选择Excel类型文件！", 1);
                return false;
            }
            else
            {
                int fileLenth = fileupload.PostedFile.ContentLength / 1024;
                if (fileLenth > 10240)
                {
                    ShowMsg("文件大小不能超过10MB", 1);
                    return false;
                }
            }
            return true;
        }
        #endregion

        #region 下载保存
        /// <summary>
        /// DownLoad数据
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnSaveExcel_Click(object sender, EventArgs e)
        {
            if (string.IsNullOrEmpty(ddlDownLoad.SelectedItem.Text))
            {
                ShowMsg("请选择保存产品种类", 1);
                ddlDownLoad.Focus();
                return;
            }

            DownLoadExcel();

        }
        /// <summary>
        /// 设置导出excel数据
        /// </summary>
        /// <param name="row"></param>
        /// <param name="buffer"></param>
        private void SetDownExcelData(DataRow row, ref StringBuilder buffer)
        {
            //产品代号
            buffer.Append(row["CUSCP_CPCODE"]);
            buffer.Append(",");
            //产品名称
            buffer.Append(row["CUSCP_Name"]);
            buffer.Append(",");

            if (ddlDownLoad.SelectedItem.Text.Equals("银行理财产品") ||
                ddlDownLoad.SelectedItem.Text.Equals("信托产品") ||
                ddlDownLoad.SelectedItem.Text.Equals("阳光私募产品") ||
                ddlDownLoad.SelectedItem.Text.Equals("私募股权产品"))
            {
                //起息日
                buffer.Append(row["CUSCP_Qixiri"]);
                buffer.Append(",");
                //到期日
                buffer.Append(row["CUSCP_Daoqiri"]);
                buffer.Append(",");
                //产品期限
                buffer.Append(row["CUSCP_Qixian"]);
                buffer.Append(",");
                //参考收益率
                buffer.Append(row["CUSCP_Shouyilv"]);
                buffer.Append(",");
            }
            if (ddlDownLoad.SelectedItem.Text.Equals("基金产品") ||
                  ddlDownLoad.SelectedItem.Text.Equals("TOT/FOF产品") ||
                  ddlDownLoad.SelectedItem.Text.Equals("境外理财产品"))
            {
                //日期
                buffer.Append(row["CUSCP_riqi"]);
                buffer.Append(",");
                //申购价
                buffer.Append(row["CUSCP_shengoujia"]);
                buffer.Append(",");
                //赎回价
                buffer.Append(row["CUSCP_shuhuijia"]);
                buffer.Append(",");
                double shengoujia = 0;
                double shuhuijia = 0;
                double average = 0;
                try
                {
                    if (row["CUSCP_shengoujia"].ToString().Length > 0 && row["CUSCP_shuhuijia"].ToString().Length > 0)
                    {
                        shengoujia = Convert.ToDouble(row["CUSCP_shengoujia"]);
                        shuhuijia = Convert.ToDouble(row["CUSCP_shuhuijia"]);

                        average = AppFunction.Average(new Double[] { shengoujia, shuhuijia });
                        average = (average - 1);

                        average = Math.Round(average, 4);
                    }
                }
                catch
                {
                    average = 0;
                }
                if (average == 0)
                {
                    //累计表现
                    buffer.Append("");
                    buffer.Append(",");
                }
                else
                {
                    //累计表现
                    buffer.Append(average);
                    buffer.Append(",");
                }
                ////累计表现
                //buffer.Append(row["CUSCP_leijibiaoxian"]);
                //buffer.Append(",");

                //excelFilter.CUSCP_SHFL = exceldt.Rows[i]["赎回费率"].ToString();

                //excelFilter.CUSCP_SGFL = exceldt.Rows[i]["申购费率"].ToString();
            }
            //产品类型
            buffer.Append(row["CUSCP_Type"]);
            buffer.Append(",");


            if (ddlDownLoad.SelectedItem.Text.Equals("境外理财产品"))
            {
                //境外投资管理人
                buffer.Append(row["CUSCP_JWTZGLR"]);
                buffer.Append(",");
            }
            else
            {
                //发行机构
                buffer.Append(row["CUSCP_FXJG"]);
                buffer.Append(",");
            }

            if (ddlDownLoad.SelectedItem.Text.Equals("银行理财产品"))
            {
                //销售管理费率
                buffer.Append(row["CUSCP_XSGLFL"]);
                buffer.Append(",");
                //销售人员佣金扣率
                buffer.Append(row["CUSCP_XSRYYJKL"]);
                buffer.Append(",");
            }

            if (ddlDownLoad.SelectedItem.Text.Equals("信托产品"))
            {
                //销售管理费率
                buffer.Append(row["CUSCP_XSGLFL"]);
                buffer.Append(",");
                //销售人员佣金扣率
                buffer.Append(row["CUSCP_XSRYYJKL"]);
                buffer.Append(",");
                //信托费率
                buffer.Append(row["CUSCP_XTFL"]);
                buffer.Append(",");
            }

            if (ddlDownLoad.SelectedItem.Text.Equals("阳光私募产品") ||
                ddlDownLoad.SelectedItem.Text.Equals("私募股权产品"))
            {
                //门槛收益率
                buffer.Append(row["CUSCP_MKSYL"]);
                buffer.Append(",");
                //超额收益率
                buffer.Append(row["CUSCP_CESYL"]);
                buffer.Append(",");
            }
            if (ddlDownLoad.SelectedItem.Text.Equals("基金产品") ||
                ddlDownLoad.SelectedItem.Text.Equals("TOT/FOF产品") ||
                ddlDownLoad.SelectedItem.Text.Equals("境外理财产品"))
            {
                //赎回费率
                buffer.Append(row["CUSCP_SHFL"]);
                buffer.Append(",");
                //申购费率
                buffer.Append(row["CUSCP_SGFL"]);
                buffer.Append(",");
            }
            //销售人员
            buffer.Append(row["CUSCP_XSRY"]);
            buffer.Append(",");
            //销售人员返佣
            buffer.Append(row["CUSCP_XSRYFY"]);
            buffer.Append(",");
            //净利润
            buffer.Append(row["CUSCP_JLR"]);
            buffer.Append(",");

            DataTable mdt = ExcelManage.Get_Member_BYMName(row["CUSCP_M_Name"].ToString());
            string name = string.Empty;
            string idcardType = string.Empty;
            string idcard = string.Empty;

            if (mdt != null && mdt.Rows.Count > 0)
            {
                name = mdt.Rows[0]["M_RealName"].ToString();
                idcardType = mdt.Rows[0]["M_IdcardType"].ToString();
                idcard = mdt.Rows[0]["M_Idcard"].ToString();
            }
            //客户姓名
            buffer.Append(name);
            buffer.Append(",");
            //证件类型
            buffer.Append(idcardType);
            buffer.Append(",");
            //身份证、护照号码
            buffer.Append(idcard);
            buffer.Append(",");
            //会员代码
            buffer.Append(row["CUSCP_M_Name"]);
            buffer.Append(",");
            //初始申购份额
            buffer.Append(row["CUSCP_CSSGFE"]);
            buffer.Append(",");
            //初始申购价格
            buffer.Append(row["CUSCP_CSSGJE"]);
            buffer.Append(",");
            //初始成本
            buffer.Append(row["CUSCP_CSCB"]);
            buffer.Append(",");
            //当前价格
            buffer.Append(row["CUSCP_DQJG"]);
            buffer.Append(",");
            //剩余份额
            buffer.Append(row["CUSCP_SYFE"]);
            buffer.Append(",");
            //产品市值
            buffer.Append(row["CUSCP_CPSZ"]);
            buffer.Append(",");
            //会员积分
            buffer.Append(row["CUSCP_HYJF"]);
            //buffer.Append("\r\n");

        }
        /// <summary>
        /// 设置导出excel标题
        /// </summary>
        /// <param name="buffer"></param>
        private void SetDownExcelHeaderData(ref StringBuilder buffer)
        {
            buffer.Append("产品代码");
            buffer.Append(",");
            buffer.Append("产品名称");
            buffer.Append(",");

            if (ddlDownLoad.SelectedItem.Text.Equals("银行理财产品") ||
                ddlDownLoad.SelectedItem.Text.Equals("信托产品") ||
                ddlDownLoad.SelectedItem.Text.Equals("阳光私募产品") ||
                ddlDownLoad.SelectedItem.Text.Equals("私募股权产品"))
            {
                buffer.Append("起息日");
                buffer.Append(",");
                buffer.Append("到期日");
                buffer.Append(",");
                buffer.Append("产品期限");
                buffer.Append(",");
                buffer.Append("参考收益率");
                buffer.Append(",");
            }
            if (ddlDownLoad.SelectedItem.Text.Equals("基金产品") ||
                  ddlDownLoad.SelectedItem.Text.Equals("TOT/FOF产品") ||
                  ddlDownLoad.SelectedItem.Text.Equals("境外理财产品"))
            {
                buffer.Append("日期");
                buffer.Append(",");
                buffer.Append("申购价");
                buffer.Append(",");
                buffer.Append("赎回价");
                buffer.Append(",");
                buffer.Append("累计表现");
                buffer.Append(",");

                //excelFilter.CUSCP_SHFL = exceldt.Rows[i]["赎回费率"].ToString();

                //excelFilter.CUSCP_SGFL = exceldt.Rows[i]["申购费率"].ToString();
            }
            buffer.Append("产品类型");
            buffer.Append(",");


            if (ddlDownLoad.SelectedItem.Text.Equals("境外理财产品"))
            {
                buffer.Append("境外投资管理人");
                buffer.Append(",");
            }
            else
            {
                buffer.Append("发行机构");
                buffer.Append(",");
            }

            if (ddlDownLoad.SelectedItem.Text.Equals("银行理财产品"))
            {
                buffer.Append("销售管理费率");
                buffer.Append(",");
                buffer.Append("销售人员佣金扣率");
                buffer.Append(",");
            }

            if (ddlDownLoad.SelectedItem.Text.Equals("信托产品"))
            {
                buffer.Append("销售管理费率");
                buffer.Append(",");
                buffer.Append("销售人员佣金扣率");
                buffer.Append(",");
                buffer.Append("信托费率");
                buffer.Append(",");
            }

            if (ddlDownLoad.SelectedItem.Text.Equals("阳光私募产品") ||
                ddlDownLoad.SelectedItem.Text.Equals("私募股权产品"))
            {
                buffer.Append("门槛收益率");
                buffer.Append(",");
                buffer.Append("超额收益率");
                buffer.Append(",");
            }
            if (ddlDownLoad.SelectedItem.Text.Equals("基金产品") ||
                             ddlDownLoad.SelectedItem.Text.Equals("TOT/FOF产品") ||
                             ddlDownLoad.SelectedItem.Text.Equals("境外理财产品"))
            {
                buffer.Append("赎回费率");
                buffer.Append(",");
                buffer.Append("申购费率");
                buffer.Append(",");
            }
            buffer.Append("销售人员");
            buffer.Append(",");
            buffer.Append("销售人员返佣");
            buffer.Append(",");
            buffer.Append("净利润");
            buffer.Append(",");

            buffer.Append("客户姓名");
            buffer.Append(",");

            buffer.Append("证件类型");
            buffer.Append(",");

            buffer.Append("身份证、护照号码");
            buffer.Append(",");

            buffer.Append("会员代码");
            buffer.Append(",");

            buffer.Append("初始申购份额");
            buffer.Append(",");

            buffer.Append("初始申购价格");
            buffer.Append(",");

            buffer.Append("初始成本");
            buffer.Append(",");

            buffer.Append("当前价格");
            buffer.Append(",");

            buffer.Append("剩余份额");
            buffer.Append(",");

            buffer.Append("产品市值");
            buffer.Append(",");

            buffer.Append("会员积分");
            //buffer.Append("\r\n");

        }
        /// <summary>
        /// 导出excel
        /// </summary>
        private void DownLoadExcel()
        {
            try
            {
                string strMapPath = Server.MapPath("~/");                                                       //获取Web应用程序的物理路径
                string sourceExcelFileName = strMapPath + @"Excel\Temp\Temp.xls";                                           //源Excel文件名
                string targetExcelFileName = strMapPath + @"Excel\" + Guid.NewGuid().ToString() + ".xls";   //使用Guid生成全局唯一字符串，作为目标Excel文件的文件名

                if (File.Exists(targetExcelFileName))
                {
                    File.Delete(targetExcelFileName);
                }

                File.Copy(sourceExcelFileName, targetExcelFileName);                                            //Copy文件，放在目标文件夹中
                 
                //Excel模型，用来操作Excel文件
                Microsoft.Office.Interop.Excel.Application excelApp = null;
                Microsoft.Office.Interop.Excel.Workbook excelWb = null;
                Microsoft.Office.Interop.Excel.Worksheet excelWs = null;
                Microsoft.Office.Interop.Excel.Range excelR = null;
                
                try
                {
                    excelApp = new Microsoft.Office.Interop.Excel.Application();
                    excelWb = excelApp.Workbooks.Open(targetExcelFileName);                             //打开Excel工作簿文件
                    excelWs = (Microsoft.Office.Interop.Excel.Worksheet)(excelWb.Sheets.get_Item(1));   //选择工作簿中第一个工作表
                   
                    StringBuilder buffer = new StringBuilder();
                    SetDownExcelHeaderData(ref buffer);
                   
                    string[] strheader = buffer.ToString().Split(',');
                    //向Excel中添加列名
                    for (int i = 0; i < strheader.Length; i++)
                    {
                        string rangeName = ((Char)(i + 65)).ToString() + "1";       //计算出单元格的位置（例：第一行第二列在Excel中为B1，即为值为1+65的字符+"1"。）
                        excelR = excelWs.get_Range(rangeName);
                        excelR.Value = strheader[i].ToString();                  //在对应单元格中写入值
                    }
                    DataTable dtbl = ExcelManage.Get_CustomerChanPinList_GetByZL(ddlDownLoad.SelectedItem.Text);
                    
                    string[] strcol = new string[strheader.Length];

                    for (int i = 0; i < dtbl.Rows.Count; i++)
                    {
                        buffer = new StringBuilder();
                        SetDownExcelData(dtbl.Rows[i], ref buffer);

                        strcol = buffer.ToString().Split(',');

                        //把每一行数据写入Excel模型中
                        for (int j = 0; j < strcol.Length; j++)
                        {
                            string rangName = ((Char)(j + 65)).ToString() + (i + 2).ToString();     //表格内容的写入从第二行开始
                            excelR = excelWs.get_Range(rangName);
                            excelR.NumberFormatLocal = "@";
                            if ("C".Equals(rangName.Substring(0, 1)))
                            {
                                excelR.NumberFormatLocal = "yyyy-MM-dd";
                            }
                            if ("D".Equals(rangName.Substring(0, 1)))
                            {
                                if (ddlDownLoad.SelectedItem.Text.Equals("基金产品") ||
                                   ddlDownLoad.SelectedItem.Text.Equals("TOT/FOF产品") ||
                                   ddlDownLoad.SelectedItem.Text.Equals("境外理财产品"))
                                {
                                    excelR.NumberFormatLocal = "0.0000";
                                }
                                else
                                {
                                    excelR.NumberFormatLocal = "yyyy-MM-dd";
                                }
                            }
                            if ("E".Equals(rangName.Substring(0, 1)))
                            {
                                if (ddlDownLoad.SelectedItem.Text.Equals("基金产品") ||
                                  ddlDownLoad.SelectedItem.Text.Equals("TOT/FOF产品") ||
                                  ddlDownLoad.SelectedItem.Text.Equals("境外理财产品"))
                                {
                                    excelR.NumberFormatLocal = "0.0000";
                                }
                                else
                                {
                                    excelR.NumberFormatLocal = "0";
                                }
                            }
                            if ("F".Equals(rangName.Substring(0, 1)))
                            {
                                excelR.NumberFormatLocal = "0.00%";
                            }
                            if ("I".Equals(rangName.Substring(0, 1)))
                            {
                                excelR.NumberFormatLocal = "0.0%";
                            }
                            if ("J".Equals(rangName.Substring(0, 1)))
                            {
                                excelR.NumberFormatLocal = "0.0%";
                            }
                            if (ddlDownLoad.SelectedItem.Text.Equals("信托产品"))
                            {
                                if ("K".Equals(rangName.Substring(0, 1)))
                                {
                                    excelR.NumberFormatLocal = "0.0%";
                                }
                                if ("Y".Equals(rangName.Substring(0, 1)))
                                {
                                    excelR.NumberFormatLocal = "0";
                                }
                            }
                            else
                            {
                                if ("X".Equals(rangName.Substring(0, 1)))
                                {
                                    excelR.NumberFormatLocal = "0";
                                }
                            }
                            excelR.Value = strcol[j];                                         //在Excel中写入对应单元格的内容
                        }
                    }                   
                    excelWb.Save();                  

                }
                catch (Exception ex)
                {
                    ShowMsg("生成失败", -1);
                    AppFunction.SaveExceptionLog(ex);
                }
                finally
                {
                    //关闭Excel，否则Excel文件将无法被打开
                    if (excelWb != null)
                    {
                        excelWb.Close();
                    }
                    if (excelApp != null)
                    {
                        excelApp.Workbooks.Close();
                        excelApp.Quit();
                    }
                }

                //向客户端发送文件...
                Response.Clear();
                Response.AddHeader("Content-Disposition", "attachment;filename=" + ddlDownLoad.SelectedItem.Text + ".xls");     //设置回发内容为Excel
                Response.ContentType = "application/ms-excel";
                Response.WriteFile(targetExcelFileName);                                        //把刚刚生成的Excel文件写入Http流

                Response.End();
            }
            catch (Exception ex)
            {
                ShowMsg(ex.Message, -1);
                AppFunction.SaveExceptionLog(ex);
            }
        }
        #endregion
    }
}